﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GBMS.MODEL;
using KPIS.GERP.GBMS.DAL;

namespace KPIS.GERP.GBMS.DAL
{
    public sealed class ProjectReaderDtlsDAL : IProjectReaderDtlsDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public void Insert(string bgm_proj_seq, string strTitle, string strDes,
                    Nullable<DateTime> dtmStart, Nullable<DateTime> dtmEnd, string strStatus,
            string strEmpSeq)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(10);

                dbManager.AddParameters(0, "@bgm_proj_seq", bgm_proj_seq);
                dbManager.AddParameters(1, "@job_title", strTitle);
                dbManager.AddParameters(2, "@job_description", strDes);
                dbManager.AddParameters(3, "@job_start_date", dtmStart);
                dbManager.AddParameters(4, "@job_end_date", dtmEnd);
                dbManager.AddParameters(5, "@job_status", strStatus);
                dbManager.AddParameters(6, "@evaluation_list_seq", 2);
                dbManager.AddParameters(7, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(8, "@created_by", strEmpSeq);
                dbManager.AddParameters(9, "@created_when", DateTime.Now); 

                strSQL = "INSERT INTO bgm_project_job_assignment"
                    + "(evaluation_list_seq, bgm_proj_seq, job_title, job_description, job_start_date, job_end_date, job_status, record_status, created_by, created_when)"
                    + " VALUES (@evaluation_list_seq, @bgm_proj_seq, @job_title, @job_description, @job_start_date, @job_end_date, @job_status, @record_status, @created_by, @created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public void Update(string strBgmProjJobAssignSeq, string bgm_proj_seq, string strTitle, string strDes
            , Nullable<DateTime> dtmStart, Nullable<DateTime> dtmEnd, string strStatus, string strEmpSeq)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(9);

                dbManager.AddParameters(0, "@seq", strBgmProjJobAssignSeq);
                dbManager.AddParameters(1, "@bgm_proj_seq", bgm_proj_seq);
                dbManager.AddParameters(2, "@job_title", strTitle);
                dbManager.AddParameters(3, "@job_description", strDes);
                dbManager.AddParameters(4, "@job_start_date", dtmStart);
                dbManager.AddParameters(5, "@job_end_date", dtmEnd);
                dbManager.AddParameters(6, "@job_status", strStatus);
                dbManager.AddParameters(7, "@updated_by", strEmpSeq);
                dbManager.AddParameters(8, "@updated_when", DateTime.Now);

                strSQL = @"UPDATE bgm_project_job_assignment
                            SET bgm_proj_seq = @bgm_proj_seq
                            ,job_title = @job_title
                            ,job_description = @job_description
                            ,job_start_date = @job_start_date
                            ,job_end_date = @job_end_date
                            ,job_status = @job_status
                            ,updated_by = @updated_by
                            ,updated_when = @updated_when
                          WHERE bgm_proj_job_assign_seq = @seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public void Delete(string strProjJobAssign)
        {
            string strSQL;

            try
            {

                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@seq", strProjJobAssign);
                dbManager.AddParameters(1, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = "UPDATE bgm_project_job_assignment SET record_status = @status WHERE bgm_proj_job_assign_seq = @seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }

            catch(Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }

        }

        public DataTable Search(string strBgmProjJobAssignSeq, string bgm_proj_seq, string strTitle, string strDes, Nullable<DateTime> strStart, Nullable<DateTime> strEnd, string strStatus)
        {
              string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@job_title", strTitle);
                dbManager.AddParameters(1, "@bgm_proj_seq", bgm_proj_seq);
                dbManager.AddParameters(2, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = @"SELECT a.bgm_proj_job_assign_seq,a.job_title,
			                coalesce(b.emp_count, 0) as emp_count,job_status_seq,
			                a.job_start_date,
			                a.job_end_date,
			                rs.job_status_name,
                            a.job_description 
                            FROM bgm_project_job_assignment a
                            LEFT JOIN (select bgm_proj_job_assign_seq,
                                count(*) as emp_count
                                from bgm_project_job_assignee
                                where record_status not like @status
                                group by bgm_proj_job_assign_seq
                                ) b 
                            ON a.bgm_proj_job_assign_seq = b.bgm_proj_job_assign_seq
                            LEFT JOIN bgm_mas_project_job_status rs
                            ON a.job_status = rs.job_status_seq
                            WHERE a.RECORD_STATUS not like @status"
                           + (!string.IsNullOrEmpty(bgm_proj_seq) ? " and bgm_proj_seq = @bgm_proj_seq" : "")
                           +" ORDER BY rs.job_status_name ";


                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                return dtb;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable Loadjobstatus()
        {
            string strSQL;
            DataTable dtb = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(0);
                /*strSQL = @"SELECT rs.job_status_seq, rs.job_status_name 
                            FROM bgm_project_job_assignment a
                            INNER JOIN bgm_mas_project_job_status rs
                            ON a.bgm_proj_job_assign_seq = rs.job_status_seq
                            ORDER BY rs.job_status_name ";*/
                strSQL = @"SELECT
	                        job_status_seq,
	                        job_status_name
                            FROM bgm_mas_project_job_status";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception jbs)
            {
                throw jbs;
            }
            finally
            {

                dbManager.Dispose();
            }
            return dtb;
        }
    }
}